package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Category;
import pos.domain.CategoryImpl;
import util.UIDGenerator;

public class JDBCCategoryDAO implements ICategoryDAO {

	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCCategoryDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}

	public List<Category> getAllCategories() {

		String sql = "SELECT * FROM category";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Category> listCategories = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listCategories = new ArrayList<Category>();

			while (result.next()) {
				Category category = new CategoryImpl();
				category.setId(result.getString("idcategory"));
				category.setName(result.getString("name"));

				listCategories.add(category);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listCategories;
	}

	public Category getCategoryById(String idcategory) {

		Category category = new CategoryImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Category with that ID.
		String sql = "SELECT * FROM category WHERE (idcategory = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idcategory);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				category.setId(result.getString("idcategory"));
				category.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return category;
	}

	public Category getCategoryByName(String name) {

		Category category = new CategoryImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Category with that ID.
		String sql = "SELECT * FROM category WHERE (name = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				category.setId(result.getString("idcategory"));
				category.setName(result.getString("name"));
				category.setDiscipline(new JDBCDisciplineDAO()
						.getDisciplineByCategory(result.getString("idcategory")));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return category;
	}

	public Category insertCategory(Category category) {

		PreparedStatement stmt = null;
		String idcategory = "";
		if (category.getId() == null) {
			idcategory = UIDGenerator.getInstance().getKey(); // ID for DB
			category.setId(idcategory); // ID for object
		} else {
			idcategory = category.getId();
			category.setId(idcategory);
		}
		if (category.getId() != null && category.getName() != null) {

			String sql = "INSERT INTO category (idcategory,name) VALUES (?,?)";

			try {
				stmt = conn.prepareStatement(sql);

				stmt.setString(1, idcategory);
				stmt.setString(2, category.getName());

				stmt.executeUpdate();

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("Error Code: " + e.getErrorCode());
				System.out.println("SQL State: " + e.getSQLState());
				System.out.println("Cause: " + e.getCause());
				e.printStackTrace();

			} finally {
				try {
					if (stmt != null) {
						conn.close();
					}
				} catch (SQLException e) {

				}
			}
		}
		return category;
	}

	public void removeCategory(Category category) {

		PreparedStatement stmt = null;

		String sql = "DELETE FROM category WHERE (idcategory = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, category.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
	}

	public boolean existCategory(String name) {

		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM category WHERE (name = ?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			if (result.next() == false) {
				res = false;
			} else {
				res = true;
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;
	}

}
